﻿<?xml version="1.0" encoding="utf-8" ?>
<tbl>

	<querynugget>
		<priority>21</priority>
		<mode>LTE</mode>
		<rowcount>7</rowcount>
		<alwayscheck>True</alwayscheck>
		<testcontext>|AlterStep12|DatabaseNeedsUpdate|</testcontext>
		<sql>
			select [table_schema], [table_name], [column_name], [ordinal_position]
			from [INFORMATION_SCHEMA].[COLUMNS]
			where [table_name] in ('vw_carrot_CategoryURL')
		</sql>
	</querynugget>

	<querynugget>
		<priority>20</priority>
		<mode>LT</mode>
		<rowcount>1</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep12|</testcontext>
		<sql>
			select p.object_id, p.[name]
			from [sys].[all_parameters] as p
			where p.[object_id] = OBJECT_ID('dbo.[carrot_UpdateGoLiveLocal]')
			and p.[name] = '@xmlDocument'
		</sql>
	</querynugget>

	<querynugget>
		<priority>20</priority>
		<mode>LT</mode>
		<rowcount>1</rowcount>
		<alwayscheck>True</alwayscheck>
		<testcontext>|AlterStep12|DatabaseNeedsUpdate|</testcontext>
		<sql>
			select distinct table_name, column_name
			from [INFORMATION_SCHEMA].[COLUMNS]
			where table_name in('carrot_Content') and column_name in('CreditUserId')
		</sql>
	</querynugget>

	<querynugget>
		<priority>18</priority>
		<mode>LT</mode>
		<rowcount>1</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep11|</testcontext>
		<sql>
			select o.name, o.type_desc
			from sys.objects o
			where o.name = 'carrot_BlogMonthlyTallies'
			AND o.object_id in (select m.object_id
			from sys.sql_modules as m
			where m.definition like '%(GoLiveDate &lt; @UTCDateTime OR @ActiveOnly = 0)%' )
		</sql>
	</querynugget>
	<querynugget>
		<priority>15</priority>
		<mode>LT</mode>
		<rowcount>26</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep10|</testcontext>
		<sql>
			select [table_schema], [table_name], [column_name], [ordinal_position]
			from [INFORMATION_SCHEMA].[COLUMNS]
			where [table_name] in ('vw_carrot_EditHistory')
		</sql>
	</querynugget>
	<querynugget>
		<priority>12</priority>
		<mode>LT</mode>
		<rowcount>34</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep09|</testcontext>
		<sql>
			select [table_schema], [table_name], [column_name], [ordinal_position]
			from [INFORMATION_SCHEMA].[COLUMNS]
			where [table_name] in ('vw_carrot_Content')
		</sql>
	</querynugget>
	<querynugget>
		<priority>13</priority>
		<mode>LT</mode>
		<rowcount>1</rowcount>
		<alwayscheck>True</alwayscheck>
		<testcontext>|AlterStep09|</testcontext>
		<sql>
			select distinct table_name, column_name
			from [INFORMATION_SCHEMA].[COLUMNS]
			where table_name in('carrot_Sites') and column_name in('Blog_DatePath')
		</sql>
	</querynugget>
	<querynugget>
		<priority>12</priority>
		<mode>LT</mode>
		<rowcount>1</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep09|AlterStep08|AlterStep07|</testcontext>
		<sql>
			select distinct table_name, column_name
			from [INFORMATION_SCHEMA].[COLUMNS]
			where table_name in('carrot_RootContent') and column_name in('ShowInSiteMap')
		</sql>
	</querynugget>
	<querynugget>
		<priority>12</priority>
		<mode>LT</mode>
		<rowcount>10</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep08|</testcontext>
		<sql>
			select distinct [view_name], [table_name], [column_name]
			from [INFORMATION_SCHEMA].[VIEW_COLUMN_USAGE]
			where [view_name] in ('vw_carrot_Comment')
		</sql>
	</querynugget>
	<querynugget>
		<priority>10</priority>
		<mode>DNEQ</mode>
		<rowcount>6</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep07|AlterStep06|</testcontext>
		<sql>
			select [specific_name], [ordinal_position], [parameter_name]
			from [INFORMATION_SCHEMA].[PARAMETERS]
			where [specific_name] like 'carrot%'
		</sql>
	</querynugget>
	<querynugget>
		<priority>11</priority>
		<mode>LT</mode>
		<rowcount>2</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep07|</testcontext>
		<sql>
			select distinct table_name, column_name
			from [INFORMATION_SCHEMA].[COLUMNS]
			where table_name in('carrot_RootContent') and column_name in('GoLiveDate', 'RetireDate')
		</sql>
	</querynugget>
	<querynugget>
		<priority>10</priority>
		<mode>LT</mode>
		<rowcount>3</rowcount>
		<alwayscheck>True</alwayscheck>
		<testcontext>|AlterStep06|</testcontext>
		<sql>
			select distinct table_name, column_name
			from [INFORMATION_SCHEMA].[COLUMNS]
			where table_name in('carrot_ContentType', 'carrot_ContentTag', 'carrot_ContentCategory') and column_name in('ContentTypeID', 'SiteID')
		</sql>
	</querynugget>
	<querynugget>
		<priority>1</priority>
		<mode>EQ</mode>
		<rowcount>3</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|IsPostStep10|</testcontext>
		<sql>
			select [table_schema], [table_name], [column_name], [ordinal_position]
			from [INFORMATION_SCHEMA].[COLUMNS]
			where [table_name] in ('carrot_DataInfo')
		</sql>
	</querynugget>
	<querynugget>
		<priority>1</priority>
		<mode>GTE</mode>
		<rowcount>36</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|IsPostStep09|</testcontext>
		<sql>
			select [table_schema], [table_name], [column_name], [ordinal_position]
			from [INFORMATION_SCHEMA].[COLUMNS]
			where [table_name] in ('vw_carrot_Content')
		</sql>
	</querynugget>
	<querynugget>
		<priority>1</priority>
		<mode>EQ</mode>
		<rowcount>4</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|IsPostStep04|</testcontext>
		<sql>
			select distinct table_name
			from [INFORMATION_SCHEMA].[COLUMNS]
			where table_name in ('carrot_Sites', 'carrot_RootContent', 'carrot_Content', 'carrot_Widget')
		</sql>
	</querynugget>
	<querynugget>
		<priority>1</priority>
		<mode>GTE</mode>
		<rowcount>4</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|DoCMSTablesExist|</testcontext>
		<sql>
			select distinct table_name
			from [INFORMATION_SCHEMA].[COLUMNS]
			where table_name in ('aspnet_Membership', 'aspnet_Users', 'tblSites', 'tblRootContent', 'carrot_Sites', 'carrot_RootContent')
		</sql>
	</querynugget>

	<querynugget>
		<priority>5</priority>
		<mode>GTE</mode>
		<rowcount>1</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|DoUsersExist|</testcontext>
		<sql>
			if exists( select * from [INFORMATION_SCHEMA].[COLUMNS] where table_name = 'aspnet_Users' ) begin
			select top 3 * from [dbo].[aspnet_Users]
			end else begin
			select top 0 GetDate() as FauxColumn
			end
		</sql>
	</querynugget>

	<querynugget>
		<priority>1</priority>
		<mode>LT</mode>
		<rowcount>68</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|DatabaseNeedsUpdate|AreCMSTablesIncomplete|</testcontext>
		<sql>
			select [table_schema], [table_name], [column_name], [ordinal_position]
			from [INFORMATION_SCHEMA].[COLUMNS]
			where [table_name] in ('carrot_Content', 'carrot_RootContent', 'carrot_SerialCache', 'carrot_Sites', 'carrot_UserSiteMapping', 'carrot_Widget', 'carrot_WidgetData')
		</sql>
	</querynugget>
	<querynugget>
		<priority>2</priority>
		<mode>GTE</mode>
		<rowcount>4</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|DatabaseNeedsUpdate|AreCMSTablesIncomplete|</testcontext>
		<sql>
			select distinct table_name
			from [INFORMATION_SCHEMA].[COLUMNS]
			where table_name in ('tblSites', 'tblRootContent', 'tblContent', 'tblWidget')
		</sql>
	</querynugget>
	<querynugget>
		<priority>1</priority>
		<mode>EQ</mode>
		<rowcount>7</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|HasCarrotPrefix|</testcontext>
		<sql>
			select distinct [table_schema], [table_name]
			from [INFORMATION_SCHEMA].[COLUMNS]
			where [table_name] in ('carrot_Content', 'carrot_RootContent', 'carrot_SerialCache', 'carrot_Sites', 'carrot_UserSiteMapping', 'carrot_Widget', 'carrot_WidgetData')
		</sql>
	</querynugget>
	<querynugget>
		<priority>3</priority>
		<mode>LT</mode>
		<rowcount>1</rowcount>
		<alwayscheck>True</alwayscheck>
		<testcontext>|PreCarrotPrefix|</testcontext>
		<sql>
			select distinct table_name
			from [INFORMATION_SCHEMA].[COLUMNS]
			where table_name in ('tblSites', 'carrot_Sites')
		</sql>
	</querynugget>
	<querynugget>
		<priority>3</priority>
		<mode>LT</mode>
		<rowcount>1</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep00|PreCarrotPrefix|</testcontext>
		<sql>
			select column_name, table_name
			from [INFORMATION_SCHEMA].[COLUMNS]
			where column_name='SerialCacheID' and table_name in ('tblSerialCache', 'carrot_SerialCache')
		</sql>
	</querynugget>
	<querynugget>
		<priority>3</priority>
		<mode>LT</mode>
		<rowcount>1</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep01|PreCarrotPrefix|</testcontext>
		<sql>
			select column_name, table_name
			from [INFORMATION_SCHEMA].[COLUMNS]
			where table_name in ('tblContent', 'carrot_Content') and column_name = 'MetaKeyword'
		</sql>
	</querynugget>
	<querynugget>
		<priority>3</priority>
		<mode>LT</mode>
		<rowcount>1</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep02|PreCarrotPrefix|</testcontext>
		<sql>
			select column_name, table_name
			from [INFORMATION_SCHEMA].[COLUMNS]
			where table_name in ('tblWidget', 'carrot_Widget') and column_name = 'Root_WidgetID'
		</sql>
	</querynugget>
	<querynugget>
		<priority>3</priority>
		<mode>LT</mode>
		<rowcount>1</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep03|PreCarrotPrefix|</testcontext>
		<sql>
			select column_name, table_name
			from [INFORMATION_SCHEMA].[COLUMNS]
			where table_name in ('tblRootContent', 'carrot_RootContent') and column_name = 'CreateDate'
		</sql>
	</querynugget>
	<querynugget>
		<priority>3</priority>
		<mode>LT</mode>
		<rowcount>6</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep04|PreCarrotPrefix|</testcontext>
		<sql>
			select column_name, table_name
			from [INFORMATION_SCHEMA].[COLUMNS]
			where table_name in ('carrot_Sites', 'carrot_RootContent')
		</sql>
	</querynugget>
	<querynugget>
		<priority>3</priority>
		<mode>LT</mode>
		<rowcount>6</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep05|</testcontext>
		<sql>
			select distinct [view_name], [table_name], [column_name]
			from [INFORMATION_SCHEMA].[VIEW_COLUMN_USAGE]
			where [view_name] in ( 'vw_carrot_Content', 'vw_carrot_Widget') and column_name in ('Root_WidgetID', 'Root_ContentID')
		</sql>
	</querynugget>
	<querynugget>
		<priority>2</priority>
		<mode>LT</mode>
		<rowcount>5</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|AlterStep05|</testcontext>
		<sql>
			select distinct [view_name], [table_name], [column_name]
			from [INFORMATION_SCHEMA].[VIEW_COLUMN_USAGE]
			where [view_name] in ( 'vw_carrot_Content', 'vw_carrot_Widget')
		</sql>
	</querynugget>
	<querynugget>
		<priority>-1</priority>
		<mode>LT</mode>
		<rowcount>1</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|SchemaVersionCheck|</testcontext>
		<sql>
			if not exists(select [table_name] from [INFORMATION_SCHEMA].[COLUMNS] where [table_name] in ('carrot_DataInfo') ) begin
			select @DataKey as [DataKey], '' [DataValue]
			end else begin
			select [DataKey], [DataValue] from dbo.[carrot_DataInfo] where [DataKey] = @DataKey
			end
		</sql>
	</querynugget>

	<querynugget>
		<priority>-1</priority>
		<mode>LT</mode>
		<rowcount>1</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|SchemaVersionUpdate|</testcontext>
		<sql>
			if exists(select [table_name] from [INFORMATION_SCHEMA].[COLUMNS] where [table_name] in ('carrot_DataInfo') ) begin

			if not exists(select * from dbo.[carrot_DataInfo] where [DataKey] = @DataKey) begin

			insert into dbo.[carrot_DataInfo]([DataKey], [DataValue])
			values (@DataKey, @DataValue)

			end else begin

			update dbo.[carrot_DataInfo]
			set [DataValue] = @DataValue
			where [DataKey] = @DataKey

			end

			end
		</sql>
	</querynugget>

	<querynugget>
		<priority>100</priority>
		<mode>LT</mode>
		<rowcount>2</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|StepXX|</testcontext>
		<sql>XXXXXXXXXXXXXXXX</sql>
	</querynugget>
	<querynugget>
		<priority>100</priority>
		<mode>LT</mode>
		<rowcount>2</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|StepXX|</testcontext>
		<sql>XXXXXXXXXXXXXXXX</sql>
	</querynugget>
	<querynugget>
		<priority>100</priority>
		<mode>LT</mode>
		<rowcount>2</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|StepXX|</testcontext>
		<sql>XXXXXXXXXXXXXXXX</sql>
	</querynugget>
	<querynugget>
		<priority>100</priority>
		<mode>LT</mode>
		<rowcount>2</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|StepXX|</testcontext>
		<sql>XXXXXXXXXXXXXXXX</sql>
	</querynugget>
	<querynugget>
		<priority>100</priority>
		<mode>LT</mode>
		<rowcount>2</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|StepXX|</testcontext>
		<sql>XXXXXXXXXXXXXXXX</sql>
	</querynugget>
	<querynugget>
		<priority>100</priority>
		<mode>LT</mode>
		<rowcount>2</rowcount>
		<alwayscheck>False</alwayscheck>
		<testcontext>|StepXX|</testcontext>
		<sql>XXXXXXXXXXXXXXXX</sql>
	</querynugget>
</tbl>